package com.meet.dao;


import com.meet.entity.ConnectionByWM;
import com.meet.util.JDBCutil;
import com.meet.vo.ConnectionVo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author King_of_hehe
 */
public class ConnectionByWMDaoImpl implements ConnectionByWMDao {

    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    /**
     * 删除连接
     *
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public int dropConnection(Integer id) throws SQLException {

        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "delete  from user_meeting where id = ?";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1,id);

        int count = preparedStatement.executeUpdate();


        JDBCutil.closeConnection();
        return count;
    }

    /**
     * 获取所有数据的总数
     *
     * @return
     * @throws SQLException
     */
    @Override
    public int totalPage() throws SQLException {
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "select count(1) from user_meeting";
        preparedStatement = conn.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        int count = 0;
        if (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        JDBCutil.closeConnection();
        return count;
    }

    /**
     * 添加数据
     *
     * @param connectionByWM
     * @return
     */
    @Override
    public int addConnection(ConnectionByWM connectionByWM) throws SQLException {
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "insert into user_meeting(user_id,meeting_id,confirm) values (?,?,?)";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1,connectionByWM.getUser_id());
        preparedStatement.setObject(2,connectionByWM.getMeeting_id());
        preparedStatement.setObject(3,connectionByWM.isConfirm());

        int count = preparedStatement.executeUpdate();

        JDBCutil.closeConnection();
        return count;
    }

    /**
     * 根据id获取连接
     *
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public ConnectionByWM getWM(Integer id) throws SQLException {
        ConnectionByWM wm = new ConnectionByWM();
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "select * from user_meeting where id = ?";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1,id);

        resultSet = preparedStatement.executeQuery();

        while(resultSet.next()){

           wm.setId(resultSet.getInt(1));
           wm.setUser_id(resultSet.getInt(2));
           wm.setMeeting_id(resultSet.getInt(3));
           wm.setConfirm(resultSet.getBoolean(4));

        }
        JDBCutil.closeConnection();
        return wm;
    }

    /**
     * 查找所有连接
     *
     * @param page
     * @param num
     * @return
     * @throws SQLException
     */
    @Override
    public List<ConnectionVo> getAllWM(int page, int num,int id) throws SQLException {
        List<ConnectionVo> list = new ArrayList<>();


        //连接数据库
        Connection conn = JDBCutil.getConnection();

//        String sql = "SELECT a.id,b.u_name,c.m_name,a.confirm FROM user_meeting a,workers b,meeting c WHERE a.user_id = b.id AND a.meeting_id = c.id and b.id = ? LIMIT ?,?";
        String sql = "select  u.id, w.u_name,  b.r_name, m.m_name,  m.t_start, m.t_end, u.confirm from user_meeting u , meeting m, boardroom b , workers w where u.meeting_id = m.id and m.r_id=b.id and w.id=m.w_id and u.user_id= ? LIMIT ?,?";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1,id);
        preparedStatement.setObject(2,page);
        preparedStatement.setObject(3,num);

        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()){
            ConnectionVo vo = new ConnectionVo();

            vo.setId(resultSet.getInt(1));
            vo.setUserName(resultSet.getString(2));
            vo.setMeetingRoomName(resultSet.getString(3));
            vo.setMeetingName(resultSet.getString(4));
            vo.setStartTime(resultSet.getTimestamp(5));
            vo.setEndTime(resultSet.getTimestamp(6));
            vo.setConfirm(resultSet.getBoolean(7));

            list.add(vo);
        }

        JDBCutil.closeConnection();
        return list;
    }



    /**
     * 更新连接
     *
     * @param wm
     * @return
     * @throws SQLException
     */
    @Override
    public int updateWM(ConnectionByWM wm) throws SQLException {
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "update user_meeting set uer_id=?,meeting_id=?,confirm=? where id=?";
        //String sql = "update boardRoom set state=?";
        preparedStatement = conn.prepareStatement(sql);

        preparedStatement.setObject(1,wm.getUser_id());
        preparedStatement.setObject(2,wm.getMeeting_id());
        preparedStatement.setObject(3,wm.isConfirm());
        preparedStatement.setObject(4,wm.getId());

        //preparedStatement.setObject(1,boardRoom.getState());

        int count = preparedStatement.executeUpdate();

        JDBCutil.closeConnection();
        return count;
    }

    /**
     * 修改是否确定
     *
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public int yes(Integer id) throws SQLException {
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "update user_meeting set confirm=? where id=?";
        //String sql = "update boardRoom set state=?";
        preparedStatement = conn.prepareStatement(sql);

        preparedStatement.setObject(2,id);
        preparedStatement.setObject(1,true);

        //preparedStatement.setObject(1,boardRoom.getState());

        int count = preparedStatement.executeUpdate();

        JDBCutil.closeConnection();
        return count;
    }




}
